package smartcrud.spring.models.cms;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import smartcrud.common.orm.Page;
import smartcrud.common.orm.PropertyFilter;
import smartcrud.common.orm.jdbc.Sql;

@Repository
public class PostDaoJdbcImpl implements PostDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Override
	public Post createPost(final Post o) {
		final StringBuffer sql = new StringBuffer("insert into CMS_POST(TITLE,CONTENT,CATEGORY_ID,USER_ID,HIT,DING,CAI,TOPIC_IMAGE_URL,SOURCE,CREATE_DATE,UPDATE_DATE)");
		sql.append(" values(?,?,?,?,?,?,?,?,?,now(),now())");
        
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(/* sql, */new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql.toString(),
						Statement.RETURN_GENERATED_KEYS);
				int z = 0;
				ps.setString(++z, o.getTitle());
				ps.setString(++z, o.getContent());
				ps.setLong(++z, o.getCategoryId());
				ps.setLong(++z, o.getUserId());
				ps.setInt(++z, o.getHit());
				ps.setInt(++z, o.getDing());
				ps.setInt(++z, o.getCai());
				ps.setString(++z, o.getTopicImageUrl());
				ps.setString(++z, o.getSource());
				return ps;
			}
		}, keyHolder);
		o.setId(keyHolder.getKey().longValue());
		return o;
	}

	@Override
	public void deletePost(Long id) {
		jdbcTemplate.update("delete from CMS_POST where ID=?", id);
	}

	@Override
	public void updatePost(Post o) {
	    final StringBuffer sql = new StringBuffer("update CMS_POST set TITLE=?,CONTENT=?,CATEGORY_ID=?,USER_ID=?,HIT=?,DING=?,CAI=?,TOPIC_IMAGE_URL=?,SOURCE=?,UPDATE_DATE=now()");
	    sql.append(" where ID=?");       
		jdbcTemplate.update(sql.toString(), 
				o.getTitle(), 
				o.getContent(), 
				o.getCategoryId(), 
				o.getUserId(), 
				o.getHit(), 
				o.getDing(), 
				o.getCai(), 
				o.getTopicImageUrl(), 
				o.getSource(), 
				o.getId() 
				);
	}

	@Override
	public Post findPostById(Long id) {
		final String sql = "select ID,TITLE,CONTENT,CATEGORY_ID,USER_ID,HIT,DING,CAI,TOPIC_IMAGE_URL,SOURCE,CREATE_DATE,UPDATE_DATE from CMS_POST where ID=?";
		return jdbcTemplate.queryForObject(sql, new RowMapper<Post>() {
			@Override
			public Post mapRow(ResultSet rs, int rowNum) throws SQLException {
				Post o = new Post();
				o.setId(rs.getLong("ID"));
				o.setTitle(rs.getString("TITLE"));
				o.setContent(rs.getString("CONTENT"));
				o.setCategoryId(rs.getLong("CATEGORY_ID"));
				o.setUserId(rs.getLong("USER_ID"));
				o.setHit(rs.getInt("HIT"));
				o.setDing(rs.getInt("DING"));
				o.setCai(rs.getInt("CAI"));
				o.setTopicImageUrl(rs.getString("TOPIC_IMAGE_URL"));
				o.setSource(rs.getString("SOURCE"));
				o.setCreateDate(rs.getDate("CREATE_DATE"));
				o.setUpdateDate(rs.getDate("UPDATE_DATE"));
				return o;
			}
		}, id);
	}

	@Override
	public List<Post> findAllPosts() {
        final String sql = "select ID,TITLE,CONTENT,CATEGORY_ID,USER_ID,HIT,DING,CAI,TOPIC_IMAGE_URL,SOURCE,CREATE_DATE,UPDATE_DATE from CMS_POST";
		return jdbcTemplate.query(sql, new RowMapper<Post>() {
			@Override
			public Post mapRow(ResultSet rs, int rowNum) throws SQLException {
				Post o = new Post();
				o.setId(rs.getLong("ID"));
				o.setTitle(rs.getString("TITLE"));
				o.setContent(rs.getString("CONTENT"));
				o.setCategoryId(rs.getLong("CATEGORY_ID"));
				o.setUserId(rs.getLong("USER_ID"));
				o.setHit(rs.getInt("HIT"));
				o.setDing(rs.getInt("DING"));
				o.setCai(rs.getInt("CAI"));
				o.setTopicImageUrl(rs.getString("TOPIC_IMAGE_URL"));
				o.setSource(rs.getString("SOURCE"));
				o.setCreateDate(rs.getDate("CREATE_DATE"));
				o.setUpdateDate(rs.getDate("UPDATE_DATE"));
				return o;
			}
		});
	}
	
    @Override
    public int count() {
        return jdbcTemplate.queryForInt("select count(1) from CMS_POST");
    }
    
	@Override
	public int count(Page<Post> page,final List<PropertyFilter> filters) {
		Sql sql = new Sql("select count(1) from CMS_POST where 1=1 ", page,filters);
		sql.build4count();
		return jdbcTemplate.queryForInt(sql.getSqlBody(), sql.getSqlParams());
	}

	@Override
	public Page<Post> findByPage(Page<Post> page, final List<PropertyFilter> filters) {
		if (page.isAutoCount()) {
			int totalCount = count(page, filters);
			page.setTotalCount(totalCount);
		}
		
		Sql sql = new Sql("select * from CMS_POST where 1=1 ", page, filters);
		sql.build();

		List<Post> content = jdbcTemplate.query(sql.getSqlBody(),
				new RowMapper<Post>() {
					@Override
					public Post mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Post o = new Post();
						o.setId(rs.getLong("ID"));
						o.setTitle(rs.getString("TITLE"));
						o.setContent(rs.getString("CONTENT"));
						o.setCategoryId(rs.getLong("CATEGORY_ID"));
						o.setUserId(rs.getLong("USER_ID"));
						o.setHit(rs.getInt("HIT"));
						o.setDing(rs.getInt("DING"));
						o.setCai(rs.getInt("CAI"));
						o.setTopicImageUrl(rs.getString("TOPIC_IMAGE_URL"));
						o.setSource(rs.getString("SOURCE"));
						o.setCreateDate(rs.getDate("CREATE_DATE"));
						o.setUpdateDate(rs.getDate("UPDATE_DATE"));
						return o;
					}
				}, sql.getSqlParams());

		page.setContent(content);
		return page;
	}
	
}
